package com.housekeeping.housekeeping.advertisingmanagement.dao.impl;/*
 * @author  pyh
 * @date  2021/1/18 0018 下午 8:16
 * @version 1.0
 * @since JDK1.8_241
 */

import com.housekeeping.housekeeping.advertisingmanagement.dao.OperationDao;
import com.housekeeping.housekeeping.advertisingmanagement.entity.AdMessages;
import com.housekeeping.housekeeping.utils.DBManager;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class OperationDaoImpl implements OperationDao {
    //添加信息
    @Override
    public int insert(AdMessages adMessages) {
        Connection connection = null;
        PreparedStatement ps = null;
        int i =0;
        try {
            connection = DBManager.getConnection();
            String sql="INSERT INTO advertise(status,name,associateditems,start_time,end_time,visitors,create_time) VALUES(?,?,?,?,?,?,?)";
            ps = connection.prepareStatement(sql);
            ps.setObject(1,adMessages.getStatus());
            ps.setObject(2,adMessages.getName());
            ps.setObject(3,adMessages.getAssociateditems());
            ps.setObject(4,adMessages.getStarttime());
            ps.setObject(5,adMessages.getEndtime());
            ps.setObject(6,adMessages.getVisitors());
            ps.setObject(7,adMessages.getCreatetime());
            i = ps.executeUpdate();
        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return i;
    }
    //修改
    @Override
    public AdMessages update(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        AdMessages messages =null;
        try {
            connection = DBManager.getConnection();
            String sql = "select * from advertise WHERE id = ?"+id;

        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return messages;
    }
    //删除
    @Override
    public int delete(int id) {
        Connection connection = null;
        PreparedStatement ps = null;
        int i = 0;
        try {
            connection = DBManager.getConnection();
            String sql = "update advertise set status = 0 WHERE id = ?";
            ps = connection.prepareStatement(sql);
            ps.setInt(1,id);
            i = ps.executeUpdate();
        }catch (Exception e){

        }finally {
            DBManager.close(ps,connection);
        }
        return i;
    }
    //总查询
    @Override
    public List<AdMessages> selectAll() {
        Connection connection = null;
        PreparedStatement ps = null;
        ResultSet resultSet = null;
        List<AdMessages> list = new ArrayList<AdMessages>();
        try {
            connection = DBManager.getConnection();
            String sql = "select * from advertise";
            ps = connection.prepareStatement(sql);
            resultSet = ps.executeQuery();
            while (resultSet.next()){
                AdMessages adMessages1 = new AdMessages();
                adMessages1.setId(resultSet.getInt(1));
                adMessages1.setStatus(resultSet.getInt(2));
                adMessages1.setName(resultSet.getString(3));
                adMessages1.setAssociateditems(resultSet.getString(4));
                adMessages1.setStarttime(resultSet.getDate(5));
                adMessages1.setEndtime(resultSet.getDate(6));
                adMessages1.setVisitors(resultSet.getInt(7));
                adMessages1.setCreatetime(resultSet.getDate(8));
                list.add(adMessages1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            DBManager.close(resultSet,ps,connection);
        }
        return list;
    }
    //模糊查询（根据名称查询）
    @Override
    public List<AdMessages> selectByKey(String name) {
        Connection connection =null;
        PreparedStatement ps =null;
        ResultSet resultSet=null;
        List<AdMessages> list = null;
        try {
            connection = DBManager.getConnection();
            String sql = "SELECT * FROM advertise WHERE name like ?";
            ps = connection.prepareStatement(sql);
            ps.setString(1,"'%"  +name+"%'");
            resultSet = ps.executeQuery();
            list = new ArrayList<>();
            while (resultSet.next()){
                AdMessages adMessages1 = new AdMessages();
                adMessages1.setId(resultSet.getInt(1));
                adMessages1.setStatus(resultSet.getInt(2));
                adMessages1.setName(resultSet.getString(3));
                adMessages1.setAssociateditems(resultSet.getString(4));
                adMessages1.setStarttime(resultSet.getDate(5));
                adMessages1.setEndtime(resultSet.getDate(6));
                adMessages1.setVisitors(resultSet.getInt(7));
                adMessages1.setCreatetime(resultSet.getDate(8));
                list.add(adMessages1);
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return list;
    }
    //总条数
    @Override
    public int selectCount(AdMessages adMessages) throws SQLException {
        Connection connection = DBManager.getConnection();
        String sql = "SELECT COUNT(*)  FROM advertise WHERE status = ?";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1,adMessages.getStatus());
        ResultSet rs = ps.executeQuery();
        int total = rs.next() ? rs.getInt(1) : 0;
        DBManager.close(connection, ps);
        return total;
    }
    //分页查询
    @Override
    public List<AdMessages> selectByPage(int limit, int size) throws SQLException {
        List<AdMessages> list = new ArrayList<>();
        Connection connection = DBManager.getConnection();
        String sql = "SELECT * FROM advertise WHERE status = 1  LIMIT ?,? ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ps.setInt(1, limit);
        ps.setInt(2, size);
        ResultSet rs = ps.executeQuery();
        while (rs.next()) {
            //之前数据发送成功了但是没有显示数据是因为这里只传了id和name,前端要传递全部数据才能膳食出来
            AdMessages adMessages1 = new AdMessages();
            adMessages1.setId(rs.getInt(1));
            adMessages1.setStatus(rs.getInt(2));
            adMessages1.setName(rs.getString(3));
            adMessages1.setAssociateditems(rs.getString(4));
            adMessages1.setStarttime(rs.getDate(5));
            adMessages1.setEndtime(rs.getDate(6));
            adMessages1.setVisitors(rs.getInt(7));
            adMessages1.setCreatetime(rs.getDate(8));
            list.add(adMessages1);
        }
        DBManager.close(rs, connection, ps);
        return list;
    }

    //总条数       -- 这个是根据老师写的方法
    @Override
    public int selectCount() throws SQLException {
        Connection connection = DBManager.getConnection();
        String sql = "SELECT COUNT(*)  FROM advertise WHERE  status = 1 ";
        PreparedStatement ps = connection.prepareStatement(sql);
        ResultSet rs = ps.executeQuery();
        int total = rs.next() ? rs.getInt(1) : 0;
        DBManager.close(connection, ps);
        return total;
    }
}
